package service

import (
	"errors"
	"fmt"
	"sort"
	"strconv"
	"strings"
	"time"

	"gitee.com/sansaniot/ssiot-core/facade/env"
	"gitee.com/sansaniot/ssiot-core/logger"
	"github.com/bytedance/sonic"
	"github.com/prometheus/common/log"
	"github.com/shopspring/decimal"
	"github.com/spf13/cast"
	"github.com/xuri/excelize/v2"
	"gorm.io/gorm"

	"ssdevice/common/constant"
	cDto "ssdevice/common/dto"
	"ssdevice/common/enums"
	"ssdevice/common/utils"
	"ssdevice/internal/device/models"
	"ssdevice/internal/device/models/command"
	"ssdevice/internal/device/models/query"
)

// 获取产品关联的设备SN
func getProductRelateSns(tx *gorm.DB, productIds, snList *[]string) error {
	dev := &models.SysDev{}
	devProduct := &models.SysDevProduct{}
	dev.Orm = tx
	devProduct.Orm = tx
	devList := make([]models.SysDev, 0)
	productList := make([]models.SysDevProduct, 0)
	if err := devProduct.FindList(&productList, cDto.MakeCondition(query.SysProductQuery{IdIn: *productIds})); err != nil {
		return err
	}
	productModels := make([]string, 0)
	for _, v := range productList {
		productModels = append(productModels, v.ProductModel)
	}
	if err := dev.FindList(&devList, cDto.MakeCondition(query.SysDevQuery{ProductModelIn: productModels})); err != nil {
		return err
	}
	for _, v := range devList {
		*snList = append(*snList, v.Sn)
	}
	return nil
}

// 切换产品模型
func changeDevProduct(tx *gorm.DB, dev *models.SysDev, toModel string) (err error) {
	// 目标产品
	prodM := &models.SysDevProduct{}
	prodM.Orm = tx
	if err = prodM.FindOne(prodM, cDto.MakeCondition(query.SysProductQuery{ProductModel: toModel})); err != nil {
		return
	}
	//if dev.Type != prodM.DeviceType {
	//	return errors.New("设备类型与所选产品不一致")
	//}
	// 不再校验不一致  直接取产品的类型
	if len(prodM.DeviceType) > 0 {
		dev.Type = prodM.DeviceType
	} else {
		return errors.New("产品类型信息异常")
	}
	dev.ProductModel = toModel
	configM := &models.SysDevConfig{}
	funcM := &models.SysDevFunc{}
	sensorM := &models.SysDevSensor{}
	systemM := &models.SysDevSystem{}
	configM.Orm = tx
	funcM.Orm = tx
	sensorM.Orm = tx
	systemM.Orm = tx
	// 先删后加，直接覆盖替换
	// 设备profile.config
	if err = configM.Delete(configM, cDto.MakeCondition(query.SysDevConfigQuery{Sn: dev.Sn})); err != nil {
		return
	}
	// 设备profile.sensor
	if err = sensorM.Delete(sensorM, cDto.MakeCondition(query.SysDevSensorQuery{Sn: dev.Sn})); err != nil {
		return
	}
	// 设备profile.system
	if err = systemM.Delete(systemM, cDto.MakeCondition(query.SysDevSystemQuery{Sn: dev.Sn})); err != nil {
		return
	}
	if err = funcM.Delete(funcM, cDto.MakeCondition(query.SysDevFuncQuery{Sn: dev.Sn})); err != nil {
		return
	}
	// 根据产品生成
	if err = configM.FindOne(configM, cDto.MakeCondition(query.SysDevConfigQuery{ProductId: prodM.Id})); err != nil {
		return
	}
	if err = funcM.FindOne(funcM, cDto.MakeCondition(query.SysDevFuncQuery{ProductId: prodM.Id})); err != nil {
		return
	}
	productSensors := make([]models.SysDevSensor, 0)
	if err = sensorM.FindList(&productSensors, cDto.MakeCondition(query.SysDevSensorQuery{ProductModel: prodM.ProductModel})); err != nil {
		return err
	}
	devConfig, devSensors, devSystem, devFunc := generateProfile(tx, dev, *configM, productSensors, *funcM, &command.DeviceInsertReq{})
	// 设备profile.config
	if err = configM.Add(devConfig); err != nil {
		return err
	}
	if err = funcM.Add(devFunc); err != nil {
		return err
	}
	constant.SensorCache.Remove(dev.Sn)
	constant.SensorInsCache.Remove(dev.Sn)
	// 设备profile.sensor
	if len(devSensors) > 0 {
		if err = sensorM.Add(devSensors); err != nil {
			return err
		}
		// sensor缓存
		tmpSensor := make(map[string]interface{})
		for _, sensor := range devSensors {
			tmpSensor[sensor.Key] = nil
		}
		constant.SensorCache.Set(dev.Sn, tmpSensor)
	}
	// 设备profile.system
	if err = systemM.Add(devSystem); err != nil {
		return err
	}
	return
}

// 查询历史数据(单/多设备)
func processHistoryData(db *gorm.DB, req *query.DevDataQuery, devSns []string) (err error, result interface{}) {
	rootKey := strings.Split(req.Key, ".")[0]
	tmp := "value::jsonb #>> '{" + strings.ReplaceAll(req.Key, ".", ",") + "}'"
	whereSql := "dev_sn in ?"
	cond := make([]interface{}, 0)
	cond = append(cond, devSns)
	// 查有这个key的行记录(最好是?判断)
	whereSql += " and value::jsonb #>> '{" + rootKey + "}' is not null "
	// 值不为空
	if req.HasValue {
		whereSql += " and " + tmp + " is not null and " + tmp + " != '' "
	}
	if len(req.StartTime) > 0 {
		whereSql += " and update_time>=?"
		// 分区键
		whereSql += " and date_time>=?"
		cond = append(cond, req.StartTime)
		cond = append(cond, req.StartTime[0:10]) // yyyy-MM-dd
	}
	if len(req.EndTime) > 0 {
		whereSql += " and update_time<=?"
		// 分区键
		whereSql += " and date_time<=?"
		cond = append(cond, req.EndTime)
		cond = append(cond, req.EndTime[0:10]) // yyyy-MM-dd
	}
	if len(req.StartTime) == 0 && len(req.EndTime) == 0 {
		// 默认限制天数范围7天 避免全表扫描
		whereSql += " and date_time>=?"
		st := time.Now().AddDate(0, 0, -6)
		cond = append(cond, st.Format("2006-01-02"))
	}
	if len(req.Value) > 0 {
		whereSql += " and " + tmp + " like ?"
		cond = append(cond, "%"+req.Value+"%")
	}
	// 默认增序
	timeOrder := utils.If(strings.ToUpper(req.TimeOrder) == "DESC", "DESC", "ASC").(string)
	// 不传时间与分页，默认查询最新1000条记录
	defaultLimit := len(req.StartTime) == 0 && len(req.EndTime) == 0 && !(req.PageIndex > 0 && req.PageSize > 0)
	if defaultLimit {
		whereSql += " ORDER BY update_time DESC limit 1000 "
	}
	// db拼接
	db = db.Model(&models.SysHistorySensorQuery{}).Select([]string{"dev_sn", tmp + " as value", "update_time"}).Where(whereSql, cond...)
	if !defaultLimit {
		db = db.Order(`"update_time" ` + timeOrder + " NULLS LAST")
	}
	// 分页or列表
	var resultList = make([]models.SysHistorySensorQuery, 0)
	var count int64
	if req.PageIndex > 0 && req.PageSize > 0 {
		offset := (req.PageIndex - 1) * req.PageSize
		if offset < 0 {
			offset = 0
		}
		if err = db.Scopes(cDto.Paginate(req.GetPageSize(), req.GetPageIndex())).Find(&resultList).Limit(-1).Offset(-1).Count(&count).Error; err != nil {
			return
		}
	} else {
		if err = db.Scan(&resultList).Error; err != nil {
			return
		}
	}
	// 设备名称
	snNameMapper := devname()
	// 处理
	for i, v := range resultList {
		resultList[i].DevName = snNameMapper[v.DevSn]
		if v.Value != nil {
			// 值类型*interface{}与string
			var strv string
			switch value := v.Value.(type) {
			case *interface{}:
				strv = (*value).(string)
			case string:
				strv = value
			}
			// json字符串处理
			if strings.HasPrefix(strv, "{") && strings.HasSuffix(strv, "}") {
				resultList[i].Value = utils.BytesToMap([]byte(strv))
			}
			// 数值处理
			if num, ne := strconv.ParseFloat(strv, 64); ne == nil {
				resultList[i].Value = num
			}
		}
	}
	// 矫正顺序
	if defaultLimit && timeOrder == "ASC" {
		sort.Slice(resultList, func(i, j int) bool {
			return time.Time(resultList[i].UpdateTime).Before(time.Time(resultList[j].UpdateTime))
		})
	}
	// 返回结果
	if req.PageIndex > 0 && req.PageSize > 0 {
		result = &cDto.Page{
			Records:   resultList,
			Total:     count,
			PageSize:  req.GetPageSize(),
			PageIndex: req.GetPageIndex(),
			Pages:     (count + int64(req.GetPageSize()) - 1) / int64(req.GetPageSize()),
			Other:     nil,
		}
	} else {
		result = resultList
	}
	return
}

func processHistorySystem(db *gorm.DB, req *query.DevDataQuery, devSns []string) (err error, result interface{}) {
	rootKey := strings.Split(req.Key, ".")[0]
	tmp := "value::jsonb #>> '{" + strings.ReplaceAll(req.Key, ".", ",") + "}'"
	whereSql := "dev_sn in ?"
	cond := make([]interface{}, 0)
	cond = append(cond, devSns)
	// 查有这个key的行记录(最好是?判断)
	whereSql += " and value::jsonb #>> '{" + rootKey + "}' is not null "
	// 值不为空
	if req.HasValue {
		whereSql += " and " + tmp + " is not null and " + tmp + " != '' "
	}
	if len(req.StartTime) > 0 {
		whereSql += " and update_time>=?"
		// 分区键
		whereSql += " and date_time>=?"
		cond = append(cond, req.StartTime)
		cond = append(cond, req.StartTime[0:10]) // yyyy-MM-dd
	}
	if len(req.EndTime) > 0 {
		whereSql += " and update_time<=?"
		// 分区键
		whereSql += " and date_time<=?"
		cond = append(cond, req.EndTime)
		cond = append(cond, req.EndTime[0:10]) // yyyy-MM-dd
	}
	if len(req.StartTime) == 0 && len(req.EndTime) == 0 {
		// 默认限制天数范围7天 避免全表扫描
		whereSql += " and date_time>=?"
		st := time.Now().AddDate(0, 0, -6)
		cond = append(cond, st.Format("2006-01-02"))
	}
	if len(req.Value) > 0 {
		whereSql += " and " + tmp + " like ?"
		cond = append(cond, "%"+req.Value+"%")
	}
	// 默认增序
	timeOrder := utils.If(strings.ToUpper(req.TimeOrder) == "DESC", "DESC", "ASC").(string)
	// 不传时间与分页，默认查询最新1000条记录
	defaultLimit := len(req.StartTime) == 0 && len(req.EndTime) == 0 && !(req.PageIndex > 0 && req.PageSize > 0)
	if defaultLimit {
		whereSql += " ORDER BY update_time DESC limit 1000 "
	}
	// db拼接
	db = db.Model(&models.SysHistorySystemQuery{}).Select([]string{"dev_sn", tmp + " as value", "update_time"}).Where(whereSql, cond...)
	if !defaultLimit {
		db = db.Order(`"update_time" ` + timeOrder + " NULLS LAST")
	}
	// 分页or列表
	var resultList = make([]models.SysHistorySystemQuery, 0)
	var count int64
	if req.PageIndex > 0 && req.PageSize > 0 {
		offset := (req.PageIndex - 1) * req.PageSize
		if offset < 0 {
			offset = 0
		}
		if err = db.Scopes(cDto.Paginate(req.GetPageSize(), req.GetPageIndex())).Find(&resultList).Limit(-1).Offset(-1).Count(&count).Error; err != nil {
			return
		}
	} else {
		if err = db.Scan(&resultList).Error; err != nil {
			return
		}
	}
	// 设备名称
	snNameMapper := devname()
	// 处理
	for i, v := range resultList {
		resultList[i].DevName = snNameMapper[v.DevSn]
		if v.Value != nil {
			// 值类型*interface{}与string
			var strv string
			switch value := v.Value.(type) {
			case *interface{}:
				strv = (*value).(string)
			case string:
				strv = value
			}
			// json字符串处理
			if strings.HasPrefix(strv, "{") && strings.HasSuffix(strv, "}") {
				resultList[i].Value = utils.BytesToMap([]byte(strv))
			}
			// 数值处理
			if num, ne := strconv.ParseFloat(strv, 64); ne == nil {
				resultList[i].Value = num
			}
		}
	}
	// 矫正顺序
	if defaultLimit && timeOrder == "ASC" {
		sort.Slice(resultList, func(i, j int) bool {
			return time.Time(resultList[i].UpdateTime).Before(time.Time(resultList[j].UpdateTime))
		})
	}
	// 返回结果
	if req.PageIndex > 0 && req.PageSize > 0 {
		result = &cDto.Page{
			Records:   resultList,
			Total:     count,
			PageSize:  req.GetPageSize(),
			PageIndex: req.GetPageIndex(),
			Pages:     (count + int64(req.GetPageSize()) - 1) / int64(req.GetPageSize()),
			Other:     nil,
		}
	} else {
		result = resultList
	}
	return
}

func devname() map[string]string {
	m := make(map[string]string)
	devcache, _ := env.Cache.HashGetAll(constant.DeviceEasyCache)
	for sn, v := range devcache {
		info := utils.BytesToMap([]byte(v))
		name := utils.If(info["name"] != nil, info["name"], "").(string)
		m[sn] = name
	}
	return m
}

func devSensorProfile(db *gorm.DB, devSensor models.SysDevSensor, delPath ...string) error {
	if len(delPath) > 0 && len(delPath[0]) > 0 {
		p := strings.ReplaceAll(delPath[0], ".", ",")
		path := "{config,sensor," + p + "}"
		path2 := strings.ReplaceAll(path, ",", ",properties,")
		sql := "update sys_dev_config set data = jsonb_delete_path(data::jsonb, '" + path + "'), schema = jsonb_delete_path(schema::jsonb, '" + path2 + "') "
		where := " where dev_sn = '%s' and product_model = '%s'"
		// 防止字符串中存在?占位符
		sql += fmt.Sprintf(where, devSensor.Sn, devSensor.ProductModel)
		if err := db.Raw(sql).Scan(&models.SysDevConfig{}).Error; err != nil {
			return err
		}
	} else {
		path := "{config,sensor}"
		dataStr := string(devSensor.Config)
		sql := "update sys_dev_config set data = jsonb_set(data::jsonb, '" + path + "', jsonb_update(data #> '" + path + "', '" + dataStr + "' :: jsonb ) ) "
		where := " where dev_sn = '%s' and product_model = '%s'"
		// 防止字符串中存在?占位符
		sql += fmt.Sprintf(where, devSensor.Sn, devSensor.ProductModel)
		if err := db.Raw(sql).Scan(&models.SysDevConfig{}).Error; err != nil {
			return err
		}
	}
	return nil
}

func deleteSensorProfile(db *gorm.DB, sensorList []models.SysDevSensor) error {
	// 先按设备/产品聚合
	snKeys := make(map[string][]string)
	prodKeys := make(map[string][]string)
	for _, sensor := range sensorList {
		sn := sensor.Sn
		pdm := sensor.ProductModel
		if len(sn) > 0 {
			if _, ok := snKeys[sn]; !ok {
				snKeys[sn] = make([]string, 0)
			}
			snKeys[sn] = append(snKeys[sn], sensor.Key)
		} else if len(pdm) > 0 {
			if _, ok := prodKeys[pdm]; !ok {
				prodKeys[pdm] = make([]string, 0)
			}
			prodKeys[pdm] = append(prodKeys[pdm], sensor.Key)
		}
	}
	// 组sql删键
	for sn, keys := range snKeys {
		delPath := ""
		sql := "update sys_dev_config set data = data::jsonb %s where dev_sn = '" + sn + "'"
		for _, key := range keys {
			delPath += " #- '{config,sensor," + key + "}' "
		}
		sql = fmt.Sprintf(sql, delPath)
		if err := db.Raw(sql).Scan(&models.SysDevConfig{}).Error; err != nil {
			return err
		}
	}
	for pm, keys := range prodKeys {
		delPath := ""
		sql := "update sys_dev_config set data = data::jsonb %s where product_model = '" + pm + "'"
		for _, key := range keys {
			delPath += " #- '{config,sensor," + key + "}' "
		}
		sql = fmt.Sprintf(sql, delPath)
		if err := db.Raw(sql).Scan(&models.SysDevConfig{}).Error; err != nil {
			return err
		}
	}
	return nil
}

// 根节点
func deleteDevSensorCache(sensorList []models.SysDevSensor) {
	if len(sensorList) == 0 {
		return
	}
	devSensors := make(map[string][]string)
	for _, sensor := range sensorList {
		if len(sensor.Sn) == 0 {
			continue
		}
		if _, ok := devSensors[sensor.Sn]; !ok {
			devSensors[sensor.Sn] = make([]string, 0)
		}
		devSensors[sensor.Sn] = append(devSensors[sensor.Sn], sensor.Key)
	}
	if len(devSensors) == 0 {
		return
	}
	for sn, keys := range devSensors {
		value, ok := constant.SensorCache.Get(sn)
		if !ok {
			continue
		}
		tmpMap := make(map[string]interface{})
		if err := utils.GobDeepCopy(value, &tmpMap); err != nil {
			logger.Error(err)
			continue
		}
		for _, key := range keys {
			delete(tmpMap, key)
		}
		if len(tmpMap) == 0 {
			constant.SensorCache.Remove(sn)
		} else {
			constant.SensorCache.Set(sn, tmpMap)
		}
	}
}

type RowPd struct {
	StartCol int
	EndCol   int
	CellVal  string
	Protocol models.SysProtocol
	NowCol   int

	ThisSn *string
	AllSns []string
}

type SortSensor []models.SysDevSensor

func (p SortSensor) Len() int {
	return len(p)
}

func (p SortSensor) Less(i, j int) bool {
	iDate := p[i].CreateTime
	jDate := p[j].CreateTime
	if iDate == nil || jDate == nil {
		return false
	}
	// 升序
	return time.Time(*iDate).Before(time.Time(*jDate))
}

func (p SortSensor) Swap(i, j int) {
	p[i], p[j] = p[j], p[i]
}

// 导出变量内容填充
func FillExportVariable(f *excelize.File, protoSensors []models.ProtoSensor) error {
	sheetNames := make([]string, 0)
	for _, ps := range protoSensors {
		sheetNames = append(sheetNames, ps.SysProtocol.Label)
	}
	// 头部单元格样式
	headerCellStyle, _ := f.NewStyle(&excelize.Style{
		Font: &excelize.Font{
			Bold: true,
		},
		Alignment: &excelize.Alignment{
			Horizontal: "center",
			Vertical:   "center",
		},
		Border: []excelize.Border{
			{Type: "left", Color: "000000", Style: 1},
			{Type: "top", Color: "000000", Style: 1},
			{Type: "bottom", Color: "000000", Style: 1},
			{Type: "right", Color: "000000", Style: 1},
		},
	})
	for i, sheetName := range sheetNames {
		_, _ = f.NewSheet(sheetName)
		headerNames := enums.DataHeaderName
		headerKeys := enums.DataHeaderKey
		freezeSize := len(headerNames)
		addSizeList := make([]int, 0)
		protoValue := protoSensors[i].SysProtocol.Value
		if protoValue == nil {
			// 默认按config显示json
			headerNames = append(headerNames, defaultColNames...)
			headerKeys = append(headerKeys, defaultColKeys...)
			freezeSize = len(headerNames)
		} else {
			if constraintDto, support := enums.ProtocolParamConstraint[*protoValue]; !support {
				log.Warnf("导出变量存在不支持的协议%v", *protoValue)
				// 默认按config显示json
				headerNames = append(headerNames, defaultColNames...)
				headerKeys = append(headerKeys, defaultColKeys...)
				freezeSize = len(headerNames)
			} else {
				includeNames := constraintDto.HeaderNames
				includeKeys := constraintDto.HeaderKeys
				if len(includeNames) > 0 && len(includeKeys) > 0 {
					headerNames = append(headerNames, includeNames...)
					headerKeys = append(headerKeys, includeKeys...)
					addSizeList = append(addSizeList, len(includeKeys))
				}
			}
		}
		// 第1行
		_ = f.InsertRows(sheetName, 1, 1)
		// 第2行
		_ = f.InsertRows(sheetName, 2, 1)
		colNum := len(headerNames)
		for n := 0; n < colNum; n++ {
			axis, _ := excelize.ColumnNumberToName(n + 1)
			_ = f.SetCellValue(sheetName, axis+"1", headerNames[n])
			_ = f.SetCellValue(sheetName, axis+"2", headerNames[n])
			_ = f.SetCellStyle(sheetName, axis+"1", axis+"1", headerCellStyle)
			_ = f.SetCellStyle(sheetName, axis+"2", axis+"2", headerCellStyle)
			_ = f.SetColWidth(sheetName, axis, axis, 15)
			// 第1、2行合并 - 固定列
			if n < freezeSize {
				_ = f.MergeCell(sheetName, axis+"1", axis+"2")
			}
		}
		rowPdList := make([]RowPd, 0)
		tmpCol := 0
		for n := 0; n < len(addSizeList); n++ {
			// 第1行合并 - 协议列
			firstCol := freezeSize + tmpCol
			lastCol := freezeSize + tmpCol + addSizeList[n] - 1
			axis1, _ := excelize.ColumnNumberToName(firstCol + 1)
			axis2, _ := excelize.ColumnNumberToName(lastCol + 1)
			_ = f.MergeCell(sheetName, axis1+"1", axis2+"1")
			_ = f.SetCellStyle(sheetName, axis1+"1", axis2+"1", headerCellStyle)
			cellValue := fmt.Sprintf("协议(%s)", *protoValue)
			_ = f.SetCellValue(sheetName, axis1+"1", cellValue)
			for c := firstCol; c < lastCol+1; c++ {
				rowPdList = append(rowPdList, RowPd{
					StartCol: firstCol,
					EndCol:   lastCol,
					CellVal:  cellValue,
					Protocol: protoSensors[i].SysProtocol,
					NowCol:   c,
				})
			}
			tmpCol += addSizeList[n]
		}
		// 若为从机则仅处理该从机的数据模型
		sortedSensors := SortSensor(protoSensors[i].Vars)
		sort.Sort(sortedSensors)
		// 单元格约束、特定样式处理
		setConstraintAndStyle(f, sheetName, headerKeys, freezeSize, rowPdList, sortedSensors)
	}
	return nil
}

var areaHeaderKeyList = []string{"yaskawa", "beckhoff", "ab", "omron", "ge", "mc", "siemens"}

func setConstraintAndStyle(f *excelize.File, sheetName string, headerKeys []string, freezeSize int, rowPdList []RowPd, sensorList []models.SysDevSensor) {
	// 数据行起始
	startRow, endRow := 3, 12000
	rowStart, rowEnd := "3", "12000"
	size := len(headerKeys)
	// 约束队列
	constraintDtoList := make([]enums.ConstraintDto, 0)
	for colIndex := 0; colIndex < size; colIndex++ {
		key := headerKeys[colIndex]
		tmpConstraint := enums.ConstraintDto{
			FirstRow: startRow,
			LastRow:  endRow,
			FirstCol: colIndex,
			LastCol:  colIndex,
		}
		axis, _ := excelize.ColumnNumberToName(colIndex + 1)
		switch key {
		case "sn":
			fallthrough
		case "key":
			tmpConstraint.CommentStr = "必填"
			fallthrough
		case "label":
			_ = f.SetColWidth(sheetName, axis, axis, 20)
			break
		case "param.type":
			tmpConstraint.CommentStr = "必填"
			dvRange := excelize.NewDataValidation(false)
			_ = dvRange.SetDropList([]string{"BIT", "BITX", "INT8", "UINT8", "INT16", "UINT16", "INT32", "UINT32", "FLOAT", "STRING"})
			tmpConstraint.Validation = dvRange
			break
		case "data":
			break
		case "param.param.read":
			tmpConstraint.CommentStr = "必填"
			dvRange := excelize.NewDataValidation(false)
			_ = dvRange.SetDropList([]string{"线圈状态", "离散输入状态", "保持寄存器", "输入寄存器"})
			tmpConstraint.Validation = dvRange
			break
		case "param.param.write":
			dvRange := excelize.NewDataValidation(false)
			_ = dvRange.SetDropList([]string{"强置单线圈", "强置多线圈", "预置单寄存器", "预置多寄存器"})
			tmpConstraint.Validation = dvRange
			break
		case "param.param.reg":
			fallthrough
		case "param.param.count":
			fg := true
			if key == "param.param.count" {
				for _, rpd := range rowPdList {
					if colIndex >= rpd.StartCol && colIndex <= rpd.EndCol && rpd.Protocol.Value != nil && utils.StrIn(*rpd.Protocol.Value, areaHeaderKeyList) {
						fg = false
						break
					}
				}
			}
			if fg {
				tmpConstraint.CommentStr = "必填"
			}
			fallthrough
		case "param.param.offset":
			dvRange := excelize.NewDataValidation(false)
			_ = dvRange.SetRange(0, 0, excelize.DataValidationTypeWhole, excelize.DataValidationOperatorGreaterThanOrEqual)
			tmpConstraint.Validation = dvRange
			break
		case "param.calc.base":
			fallthrough
		case "param.calc.mult":
			break
		case "param.param.area":
			var thisProtocolType string
			for _, rpd := range rowPdList {
				if colIndex >= rpd.StartCol && colIndex <= rpd.EndCol && rpd.Protocol.Value != nil && utils.StrIn(*rpd.Protocol.Value, areaHeaderKeyList) {
					thisProtocolType = *rpd.Protocol.Value
					break
				}
			}
			if len(thisProtocolType) > 0 {
				var areaList []string
				if "siemensPPI" == thisProtocolType {
					areaList = []string{"数据存储区V", "输入继电器I", "输出继电器Q", "辅助继电器M", "定时器T", "计数器C", "模拟输入映像寄存器AI", "模拟输出映像寄存器AQ", "顺序控制继电器S"}
				} else if strings.HasPrefix(thisProtocolType, "siemens") {
					// siemens其他协议含DBX excel暂时不处理下拉
					break
				} else if thisProtocolType == "mc" || thisProtocolType == "ge" || thisProtocolType == "omron" || thisProtocolType == "ab" || thisProtocolType == "beckhoff" || thisProtocolType == "yaskawa" {
					tmpConstraint.CommentStr = "必填"
					break
				} else if "mitsubishi_fxprograme" == thisProtocolType {
					areaList = []string{"输出继电器Y", "辅助继电器M", "输入继电器X", "状态S", "数据寄存器D", "定时器触点TS", "定时器当前值TN", "计数器触点CS", "计数器当前值CN", "计数器(16位)CW", "计数器(32位)CD"}
				} else if "mitsubishi_fxlink" == thisProtocolType {
					areaList = []string{"输出继电器Y", "辅助继电器M", "输入继电器X", "状态S", "数据寄存器D", "定时器触点TS", "定时器当前值TN", "计数器触点CS", "计数器当前值CN"}
				} else if strings.HasPrefix(thisProtocolType, "omron_") {
					areaList = []string{"I/O存储区CIO", "工作区W", "保持区H", "辅助区A", "定时器区T", "计数器区C", "链接继电器区L", "数据存储区D"}
				}
				dvRange := excelize.NewDataValidation(false)
				_ = dvRange.SetDropList(areaList)
				tmpConstraint.Validation = dvRange
				_ = f.SetColWidth(sheetName, axis, axis, 25)
			}
			break
		default:
			break
		}
		if len(tmpConstraint.CommentStr) > 0 {
			whichRow := strconv.Itoa(utils.If(colIndex < freezeSize, 1, 2).(int))
			_ = f.AddComment(sheetName, excelize.Comment{
				Cell: axis + whichRow,
				Text: tmpConstraint.CommentStr,
			})
		}
		if tmpConstraint.Validation != nil {
			constraintDtoList = append(constraintDtoList, tmpConstraint)
		}
	}
	for _, v := range constraintDtoList {
		axis, _ := excelize.ColumnNumberToName(v.FirstCol + 1)
		v.Validation.Sqref = axis + rowStart + ":" + axis + rowEnd
		if v.Validation.Type == "decimal" || v.Validation.Type == "whole" {
			v.Validation.SetError(excelize.DataValidationErrorStyleStop, "", "需要合法的数值")
		} else {
			v.Validation.SetError(excelize.DataValidationErrorStyleStop, "", "需填充指定内容")
		}
		_ = f.AddDataValidation(sheetName, v.Validation)
	}
	// 填充已有数据变量
	sensorSize := len(sensorList)
	rowPdMap := make(map[int]RowPd)
	for i, rpd := range rowPdList {
		rowPdMap[rpd.NowCol] = rowPdList[i]
	}
	for row := startRow; row-startRow < sensorSize; row++ {
		_ = f.InsertRows(sheetName, row, 1)
		devSensor := sensorList[row-startRow]
		sensorConfig := utils.GetMapValueFiled(utils.BytesToMap(devSensor.Config), devSensor.Key)
		paramCalc := utils.GetMapValueFiled(sensorConfig, "calc")
		paramParam := utils.GetMapValueFiled(sensorConfig, "param")
		rowStr := strconv.Itoa(row)
		for colIndex := 0; colIndex < size; colIndex++ {
			var protocol models.SysProtocol
			if rpd, ok := rowPdMap[colIndex]; ok {
				protocol = rpd.Protocol
			}
			key := headerKeys[colIndex]
			axis, _ := excelize.ColumnNumberToName(colIndex + 1)
			switch key {
			case "label":
				_ = f.SetCellValue(sheetName, axis+rowStr, devSensor.Label)
				break
			case "key":
				_ = f.SetCellValue(sheetName, axis+rowStr, devSensor.Key)
				break
			case "sn":
				_ = f.SetCellValue(sheetName, axis+rowStr, devSensor.Sn)
				break
			case "data":
				_ = f.SetCellValue(sheetName, axis+rowStr, utils.GetStringValueFiled(utils.BytesToMap(devSensor.Data), devSensor.Key))
				break
			case "config":
				_ = f.SetColWidth(sheetName, axis, axis, 120)
				_ = f.SetCellValue(sheetName, axis+rowStr, utils.GetStringValueFiled(utils.BytesToMap(devSensor.Config), devSensor.Key))
				break
			case "param.unit":
				if sensorConfig != nil {
					_ = f.SetCellValue(sheetName, axis+rowStr, cast.ToString(sensorConfig["unit"]))
				}
				break
			case "param.type":
				if sensorConfig != nil {
					_ = f.SetCellValue(sheetName, axis+rowStr, strings.ToUpper(cast.ToString(sensorConfig["type"])))
				}
				break
			case "param.param.read":
				if paramParam != nil && paramParam["read"] != nil {
					_ = f.SetCellValue(sheetName, axis+rowStr, enums.GetFuncByVal(int(paramParam["read"].(float64))))
				}
				break
			case "param.param.write":
				if paramParam != nil && paramParam["write"] != nil {
					_ = f.SetCellValue(sheetName, axis+rowStr, enums.GetControlByVal(int(paramParam["write"].(float64))))
				}
				break
			case "param.param.reg":
				if paramParam != nil && paramParam["reg"] != nil {
					_ = f.SetCellValue(sheetName, axis+rowStr, strconv.Itoa(int(paramParam["reg"].(float64))))
				}
				break
			case "param.param.count":
				if paramParam != nil && paramParam["count"] != nil {
					_ = f.SetCellValue(sheetName, axis+rowStr, strconv.Itoa(int(paramParam["count"].(float64))))
				}
				break
			case "param.param.offset":
				if paramParam != nil && paramParam["offset"] != nil {
					_ = f.SetCellValue(sheetName, axis+rowStr, strconv.Itoa(int(paramParam["offset"].(float64))))
				}
				break
			case "param.calc.base":
				if paramCalc != nil && paramCalc["base"] != nil {
					_ = f.SetCellValue(sheetName, axis+rowStr, cast.ToString(paramCalc["base"]))
				}
				break
			case "param.calc.mult":
				if paramCalc != nil && paramCalc["mult"] != nil {
					_ = f.SetCellValue(sheetName, axis+rowStr, cast.ToString(paramCalc["mult"]))
				}
				break
			case "param.param.area":
				if protocol.Value != nil && utils.StrIn(*protocol.Value, areaHeaderKeyList) && paramParam != nil && paramParam["area"] != nil {
					var areaVal string
					if "siemensPPI" == *protocol.Value {
						areaVal = enums.GetSiemensAreaByVal(paramParam["area"].(string))
					} else if strings.HasPrefix(*protocol.Value, "siemens") || *protocol.Value == "mc" || *protocol.Value == "ge" || *protocol.Value == "omron" || *protocol.Value == "ab" || *protocol.Value == "beckhoff" || *protocol.Value == "yaskawa" {
						// siemens其他协议含DBX excel暂时不处理下拉
						areaVal = paramParam["area"].(string)
					} else if strings.HasPrefix(*protocol.Value, "mitsubishi_") {
						areaVal = enums.GetMitsubshiAreaByVal(paramParam["area"].(string))
					} else if strings.HasPrefix(*protocol.Value, "omron_") {
						areaVal = enums.GetOmronAreaByVal(paramParam["area"].(string))
					}
					if len(areaVal) > 0 {
						_ = f.SetCellValue(sheetName, axis+rowStr, areaVal)
					}
				}
				break
			default:
				break
			}
		}
	}
}

var defaultColNames = []string{"变量配置"}
var defaultColKeys = []string{"config"}

func ImportVariableData(sensorM *models.SysDevSensor, f *excelize.File, userId string) (err error) {
	sheetNum := f.SheetCount
	if sheetNum == 0 {
		return
	}
	// 已有设备
	devModel := &models.SysDev{}
	devModel.Orm = sensorM.Orm
	existSns, snDevMap := devModel.GetDevSnList()
	// 协议
	protoM := &models.SysProtocol{}
	protoM.Orm = sensorM.Orm
	protoValueMap := make(map[string]models.SysProtocol)
	protoLabelMap := make(map[string]models.SysProtocol)
	var protoResult interface{}
	if err, protoResult = protoM.FindProtoList(&query.SysProtoQuery{}); err != nil {
		return
	} else {
		protoList := protoResult.([]models.SysProtocol)
		// 增加一个未知协议的项
		protoList = append(protoList, models.SysProtocol{
			Id:    100000,
			Pid:   0,
			Label: "未知协议",
		})
		for i, protocol := range protoList {
			if protocol.Value != nil {
				protoValueMap[*protocol.Value] = protoList[i]
			}
			if len(protocol.Label) > 0 {
				protoLabelMap[protocol.Label] = protoList[i]
			}
		}
	}
	// 解析
	vars := make([]models.SysDevSensor, 0)
	errorInfo := ""
	for _, sheetName := range f.GetSheetList() {
		protocol, ok := protoLabelMap[sheetName]
		if !ok {
			return errors.New("无效模板(sheet不一致)，请重新下载模板")
		}

		// 遍历设备
		rowIndexRead := make([]int, 0)
		sheetErrPre := fmt.Sprintf("<br>(sheet: %s)<br>", sheetName)

		headerNames := enums.DataHeaderName
		headerKeys := enums.DataHeaderKey
		protoValue := protocol.Value
		if protoValue == nil {
			// 默认按config显示json
			headerNames = append(headerNames, defaultColNames...)
			headerKeys = append(headerKeys, defaultColKeys...)
		} else {
			if constraintDto, support := enums.ProtocolParamConstraint[*protoValue]; !support {
				log.Warnf("导出变量存在不支持的协议%v", *protoValue)
				// 默认按config显示json
				headerNames = append(headerNames, defaultColNames...)
				headerKeys = append(headerKeys, defaultColKeys...)
			} else {
				includeNames := constraintDto.HeaderNames
				includeKeys := constraintDto.HeaderKeys
				if len(includeNames) > 0 && len(includeKeys) > 0 {
					headerNames = append(headerNames, includeNames...)
					headerKeys = append(headerKeys, includeKeys...)
				}
			}
		}
		// 按行获取[][]单元格值，f.Rows迭代器不能过滤掉空内容的行
		var rows, cols [][]string
		rows, err = f.GetRows(sheetName)
		if err != nil {
			fmt.Println(err)
		}
		cols, err = f.GetCols(sheetName)
		if err != nil {
			fmt.Println(err)
		}
		maxRowNumber := len(rows)
		maxColNumber := len(cols)
		if maxRowNumber <= 2 {
			errorInfo += fmt.Sprintf("模板内容为空(sheet:%s)", sheetName)
			continue
		}
		if maxColNumber != len(headerNames) {
			return errors.New("无效的导入文件，请重新导出文件填写内容")
		}
		for rowIndex, rowColumns := range rows {
			if utils.IntIn(rowIndex, rowIndexRead) {
				// 效率优化 (每sheet中 遍历过的行跳过)
				continue
			}
			if rowIndex == 0 {
				rowIndexRead = append(rowIndexRead, rowIndex)
				continue
			}
			if rowIndex == 1 {
				if len(rowColumns) != len(headerNames) {
					return errors.New("无效的模板抬头，请重新下载模板")
				}
				// 第2行 列对比(防止非最新模板 或 不对应的模板导入)
				if !isValidTemplate(rowColumns, headerNames) {
					return errors.New("无效的模板列，请重新下载模板")
				}
				rowIndexRead = append(rowIndexRead, rowIndex)
				continue
			}
			// 第3行起 为数据内容 解析行数据
			rowDataParseDto := parseRowData(rowColumns, headerKeys, existSns, snDevMap, protocol, userId)
			if rowDataParseDto.NotSn {
				continue
			}
			rowIndexRead = append(rowIndexRead, rowIndex)
			errMsg := rowDataParseDto.Error
			if len(errMsg) > 0 {
				tmpErrStr := fmt.Sprintf("第%s行:<br>%s", strconv.Itoa(rowIndex+1), errMsg)
				if !strings.Contains(errorInfo, sheetErrPre) {
					errorInfo += sheetErrPre
				}
				if !strings.Contains(errorInfo, tmpErrStr) {
					errorInfo += tmpErrStr
				}
				continue
			}
			devVariable := rowDataParseDto.SysDevVariable
			vars = append(vars, devVariable)
		}
	}
	if len(errorInfo) > 0 {
		return errors.New(errorInfo)
	}
	if len(vars) == 0 {
		return errors.New("变量无更新或已被清理")
	}
	// 去重
	sensorList := make([]models.SysDevSensor, 0)
	for i, v1 := range vars {
		flag := true
		for j, v2 := range sensorList {
			if v2.Key == v1.Key && v2.Sn == v1.Sn {
				flag = false
				// 将config、param合并
				tmpConfigMap := utils.BytesToMap(v1.Config)
				configMap := utils.BytesToMap(v2.Config)
				utils.DeepMerge(tmpConfigMap, configMap)
				sensorList[j].Config = utils.MapToBytes(configMap)
				tmpParamMap := utils.BytesToMap(v1.Param)
				paramMap := utils.BytesToMap(v2.Param)
				utils.DeepMerge(tmpParamMap, paramMap)
				sensorList[j].Param = utils.MapToBytes(paramMap)
			}
		}
		if flag {
			sensorList = append(sensorList, vars[i])
		}
	}
	// 按sn聚合
	snVars := make(map[string][]models.SysDevSensor)
	for i, devSensor := range sensorList {
		if _, ok := snVars[devSensor.Sn]; !ok {
			snVars[devSensor.Sn] = make([]models.SysDevSensor, 0)
		}
		snVars[devSensor.Sn] = append(snVars[devSensor.Sn], sensorList[i])
	}
	err = sensorM.Orm.Transaction(func(tsx *gorm.DB) error {
		sensorM.Orm = tsx
		for sn, devSensors := range snVars {
			// 保存/更新
			if err = sensorM.ImportUpsert(sn, devSensors); err != nil {
				return err
			}
		}
		return nil
	})
	return
}

func isValidTemplate(row2Columns, headerNames []string) bool {
	for i, colCell := range row2Columns {
		if colCell != headerNames[i] {
			return false
		}
	}
	return true
}

type RowDataParseDto struct {
	SysDevVariable models.SysDevSensor
	Error          string
	NotSn          bool
}

var offsetHeaderKeyList = []string{"mitsubishi_fxprograme", "omron_hostlink_cmode", "omron_hostlink_fins", "omron_fins"}

// 导入变量 - 解析行数据
func parseRowData(rowColumns, headerKeys, existSns []string, snDevMap map[string]models.SysDev, protocol models.SysProtocol, userId string) (rowDataParseDto RowDataParseDto) {
	errStr := ""
	devSensor := models.SysDevSensor{}
	maxCol := len(headerKeys)
	length := len(rowColumns)
	configJson := make(map[string]interface{})
	for colIndex := 0; colIndex < maxCol; colIndex++ {
		headerKey := headerKeys[colIndex]
		cellVal := ""
		if colIndex < length {
			cellVal = rowColumns[colIndex]
		}
		cellVal = strings.TrimSpace(cellVal)
		axis, _ := excelize.ColumnNumberToName(colIndex + 1)
		var tmpParam map[string]interface{}
		paramKey := ""
		switch headerKey {
		case "sn":
			if len(strings.ReplaceAll(cellVal, " ", "")) == 0 {
				errStr += fmt.Sprintf("%s列-%s<br>", axis, "设备SN(必填)")
				break
			}
			if !utils.StrIn(cellVal, existSns) {
				// 忽略不存在的设备
				rowDataParseDto.NotSn = true
				return
			}
			if snDevMap[cellVal].DeptId == constant.PublicGroupId && userId != "1" {
				errStr += fmt.Sprintf("%s列-%s<br>", axis, "没权限操作公共分组资源")
				break
			}
			devSensor.Sn = cellVal
			break
		case "label":
			devSensor.Label = cellVal
			break
		case "key":
			if len(strings.ReplaceAll(cellVal, " ", "")) == 0 {
				errStr += fmt.Sprintf("%s列-%s<br>", axis, "变量标识(必填)")
				break
			}
			devSensor.Key = cellVal
			break
		case "data":
			devSensor.Data = utils.MapToBytes(map[string]interface{}{devSensor.Key: utils.GetStringProtoInterface(cellVal)})
			break
		case "updateTime":
			break
		// 协议配置config处理
		case "param.type":
			if len(strings.ReplaceAll(cellVal, " ", "")) == 0 {
				errStr += fmt.Sprintf("%s列-%s<br>", axis, "变量类型(必填)")
				break
			}
			paramKey = strings.Replace(headerKey, "param.", "", 1) // replaceFirst
			tmpParam = make(map[string]interface{})
			tmpParam[paramKey] = strings.ToLower(cellVal)
			if "FLOAT" == cellVal {
				tmpParam["calc.decimal"] = 3
			}
			break
		case "param.unit":
			paramKey = strings.Replace(headerKey, "param.", "", 1) // replaceFirst
			tmpParam = make(map[string]interface{})
			tmpParam[paramKey] = cellVal
			break
		case "param.calc.base":
			fallthrough
		case "param.calc.mult":
			fallthrough
		case "param.param.reg":
			fallthrough
		case "param.param.count":
			fg := true
			if headerKey == "param.param.count" {
				if protocol.Value != nil && utils.StrIn(*protocol.Value, areaHeaderKeyList) {
					fg = false
				}
			}
			if fg && len(strings.ReplaceAll(cellVal, " ", "")) == 0 {
				errStr += fmt.Sprintf("%s列-%s<br>", axis, "必填")
				break
			}
			fallthrough
		case "param.param.read":
			fallthrough
		case "param.param.write":
			fallthrough
		case "param.param.offset":
			if len(strings.ReplaceAll(cellVal, " ", "")) == 0 {
				if "param.param.read" == headerKey {
					errStr += fmt.Sprintf("%s列-%s<br>", axis, "必填")
				}
				break
			}
			if "param.param.read" == headerKey {
				cellVal = strconv.Itoa(enums.GetFuncByName(cellVal))
			}
			if "param.param.write" == headerKey {
				cellVal = strconv.Itoa(enums.GetControlByName(cellVal))
			}
			if "param.param.offset" == headerKey {
				if protocol.Value != nil && utils.StrIn(*protocol.Value, offsetHeaderKeyList) {
					offsetVal := 0
					if "mitsubishi_fxprograme" == *protocol.Value {
						offsetVal = enums.GetMitsubshiOffsetByType(cellVal)
					} else if strings.HasPrefix(*protocol.Value, "omron_") {
						offsetVal = 0 // omron协议内部偏移只能为0
					}
					cellVal = strconv.Itoa(offsetVal)
				}
			}
			if _, e := strconv.ParseFloat(cellVal, 64); e != nil {
				errStr += fmt.Sprintf("%s列-%s<br>", axis, "非法数值")
				break
			}
			paramKey = strings.Replace(headerKey, "param.", "", 1) // replaceFirst
			tmpParam = make(map[string]interface{})
			dec, _ := decimal.NewFromString(cellVal)
			tmpParam[paramKey], _ = dec.Float64()
			break
		case "param.param.area":
			if len(strings.ReplaceAll(cellVal, " ", "")) == 0 {
				break
			}
			paramKey = strings.Replace(headerKey, "param.", "", 1) // replaceFirst
			tmpParam = make(map[string]interface{})
			tmpParam[paramKey] = cellVal
			if "param.param.area" == headerKey {
				if protocol.Value != nil && utils.StrIn(*protocol.Value, areaHeaderKeyList) {
					areaVal := ""
					if "siemensPPI" == *protocol.Value {
						areaVal = enums.GetSiemensAreaByName(cellVal)
					} else if strings.HasPrefix(*protocol.Value, "siemens") {
						// siemens其他协议含DBX excel暂时不处理下拉
						break
					} else if strings.HasPrefix(*protocol.Value, "mitsubishi_") {
						areaVal = enums.GetMitsubshiAreaByName(cellVal)
					} else if strings.HasPrefix(*protocol.Value, "omron_") {
						areaVal = enums.GetOmronAreaByName(cellVal)
					}
					tmpParam[paramKey] = areaVal
				}
			}
			break
		case "config":
			cellVal = strings.ReplaceAll(cellVal, " ", "")
			if len(cellVal) == 0 {
				break
			}
			var m = make(map[string]interface{})
			if err := sonic.Unmarshal([]byte(cellVal), &m); err != nil {
				errStr += fmt.Sprintf("%s列-%s<br>", axis, "变量配置内容非法")
				break
			}
			devSensor.Config = utils.MapToBytes(map[string]interface{}{devSensor.Key: m})
			break
		case "param":
			cellVal = strings.ReplaceAll(cellVal, " ", "")
			if len(cellVal) == 0 {
				break
			}
			var m = make(map[string]interface{})
			if err := sonic.Unmarshal([]byte(cellVal), &m); err != nil {
				errStr += fmt.Sprintf("%s列-%s<br>", axis, "变量扩展信息非法")
				break
			}
			devSensor.Param = utils.MapToBytes(m)
			break
		default:
			break
		}
		paramJson := make(map[string]interface{})
		if tmpParam != nil {
			paramJson = utils.FixJsonKeyDot(tmpParam)
		}
		utils.DeepMerge(paramJson, configJson)
	}
	if len(devSensor.Label) == 0 {
		devSensor.Label = devSensor.Key
	}
	if len(devSensor.Key) > 0 {
		devSensor.Config = utils.MapToBytes(map[string]interface{}{devSensor.Key: configJson})
		if devSensor.Data == nil {
			devSensor.Data = utils.MapToBytes(map[string]interface{}{devSensor.Key: nil})
		}
	}
	if devSensor.Param == nil {
		devSensor.Param = utils.MapToBytes(map[string]interface{}{})
	}
	// 返回行解析
	if len(errStr) == 0 && len(devSensor.Sn) > 0 && len(devSensor.Key) > 0 {
		// 数据组装返回
		rowDataParseDto = RowDataParseDto{
			SysDevVariable: devSensor,
		}
	} else if len(errStr) > 0 {
		// 错误信息返回
		rowDataParseDto = RowDataParseDto{
			Error: errStr,
		}
	}
	return
}

func autoDevProduct(db *gorm.DB, autoInsert *command.ProductInsertReq) (err error) {
	// 自动创建一个产品
	if autoInsert == nil {
		return
	}
	pdservice := &Product{}
	pdservice.Orm = db
	pdservice.SetAllModel()
	if err, _ = pdservice.AddProduct(autoInsert); err != nil {
		err = errors.New("自动创建设备接入model产品 发生异常 " + err.Error())
	}
	return
}

func getProductMeta(db *gorm.DB, req *query.SysProductQuery) (content []byte, err error) {
	productModel := models.SysDevProduct{}
	productModel.Orm = db
	products := make([]models.SysDevProduct, 0)
	productQuery := query.SysProductQuery{}
	if len(req.ProductModelIn) > 0 {
		productQuery.ProductModelIn = req.ProductModelIn
	}
	_ = productModel.FindList(&products, cDto.MakeCondition(productQuery))
	pdModels := make([]string, 0)
	pdIds := make([]string, 0)
	for _, pd := range products {
		pdModels = append(pdModels, pd.ProductModel)
		pdIds = append(pdIds, pd.Id)
	}
	if len(pdModels) == 0 {
		err = errors.New("查无数据")
		return
	}
	// 属性
	sensors := make([]models.SysDevSensor, 0)
	configs := make([]models.SysDevConfig, 0)
	funcs := make([]models.SysDevFunc, 0)
	sensorModel := models.SysDevSensor{}
	configModel := models.SysDevConfig{}
	funcModel := models.SysDevFunc{}
	sensorModel.Orm = db
	configModel.Orm = db
	funcModel.Orm = db
	if err = sensorModel.FindList(&sensors, cDto.MakeCondition(query.SysDevSensorQuery{ProductModelIn: pdModels})); err != nil {
		return
	}
	if err = configModel.FindList(&configs, cDto.MakeCondition(query.SysDevConfigQuery{ProductIdIn: pdIds})); err != nil {
		return
	}
	if err = funcModel.FindList(&funcs, cDto.MakeCondition(query.SysDevFuncQuery{ProductIdIn: pdIds})); err != nil {
		return
	}
	pdSensors := make(map[string][]map[string]interface{})
	pdConfig := make(map[string]map[string]interface{})
	pdFunc := make(map[string]map[string]interface{})
	for _, sensor := range sensors {
		if _, ok := pdSensors[sensor.ProductModel]; !ok {
			pdSensors[sensor.ProductModel] = make([]map[string]interface{}, 0)
		}
		tmp := map[string]interface{}{
			"id":           sensor.Id,
			"sn":           sensor.Sn,
			"productModel": sensor.ProductModel,
			"key":          sensor.Key,
			"label":        sensor.Label,
			"type":         sensor.Type,
			"data":         utils.BytesToMap(sensor.Data),
			"index":        sensor.Index,
			"schema":       utils.BytesToMap(sensor.Schema),
			"config":       utils.BytesToMap(sensor.Config),
			"param":        utils.BytesToMap(sensor.Param),
		}
		if sensor.CreateTime != nil {
			tmp["createTime"] = utils.FormatTimeToStr(time.Time(*sensor.CreateTime))
		}
		if sensor.UpdateTime != nil {
			tmp["updateTime"] = utils.FormatTimeToStr(time.Time(*sensor.UpdateTime))
		}
		pdSensors[sensor.ProductModel] = append(pdSensors[sensor.ProductModel], tmp)
	}
	for _, config := range configs {
		pdConfig[config.ProductModel] = map[string]interface{}{
			"id":           config.Id,
			"devSn":        config.DevSn,
			"data":         utils.BytesToMap(config.Data),
			"schema":       utils.BytesToMap(config.Schema),
			"productModel": config.ProductModel,
			"devProductId": config.DevProductId,
		}
		if config.CreateTime != nil {
			pdConfig[config.ProductModel]["createTime"] = utils.FormatTimeToStr(time.Time(*config.CreateTime))
		}
		if config.UpdateTime != nil {
			pdConfig[config.ProductModel]["updateTime"] = utils.FormatTimeToStr(time.Time(*config.UpdateTime))
		}
	}
	for _, fu := range funcs {
		pdFunc[fu.ProductModel] = map[string]interface{}{
			"id":           fu.Id,
			"devSn":        fu.DevSn,
			"data":         utils.BytesToMap(fu.Data),
			"schema":       utils.BytesToMap(fu.Schema),
			"productModel": fu.ProductModel,
			"devProductId": fu.DevProductId,
		}
		if fu.CreateTime != nil {
			pdFunc[fu.ProductModel]["createTime"] = utils.FormatTimeToStr(time.Time(*fu.CreateTime))
		}
		if fu.UpdateTime != nil {
			pdFunc[fu.ProductModel]["updateTime"] = utils.FormatTimeToStr(time.Time(*fu.UpdateTime))
		}
	}
	// 生成结果
	result := make([]map[string]interface{}, 0)
	for _, pd := range products {
		sensorMaps := pdSensors[pd.ProductModel]
		configMap := pdConfig[pd.ProductModel]
		funcMap := pdFunc[pd.ProductModel]
		if len(configMap) == 0 {
			continue
		}
		//if strings.HasPrefix(pd.Photo, "sansan/") {
		//	// 远程上传文件是admin的1/1
		//	pd.Photo = "sansan/1/1" + pd.Photo[strings.LastIndex(pd.Photo, "/"):]
		//}
		productMap := map[string]interface{}{
			"id":          pd.Id,
			"name":        pd.Name,
			"deviceType":  pd.DeviceType,
			"status":      pd.Status,
			"model":       pd.ProductModel,
			"deptId":      pd.DeptId,
			"photo":       pd.Photo,
			"description": pd.Description,
			"view":        utils.BytesToMap(pd.View),
		}
		if pd.CreateTime != nil {
			productMap["createTime"] = utils.FormatTimeToStr(time.Time(*pd.CreateTime))
		}
		if pd.UpdateTime != nil {
			productMap["updateTime"] = utils.FormatTimeToStr(time.Time(*pd.UpdateTime))
		}
		contentMap := map[string]interface{}{
			"product": productMap,
			"sensors": sensorMaps,
			"config":  configMap,
			"func":    funcMap,
		}
		result = append(result, contentMap)
	}
	content = utils.MapArrToBytes(result)
	return
}

func importProductMeta(db *gorm.DB, contentMapArr []map[string]interface{}) (err error) {
	return db.Transaction(func(tx *gorm.DB) error {
		for _, contentInfo := range contentMapArr {
			// 数据内容
			pdInfo := contentInfo["product"].(map[string]interface{})
			configInfo := contentInfo["config"].(map[string]interface{})
			funcInfo := contentInfo["func"].(map[string]interface{})
			sensorInfos := make([]interface{}, 0)
			if contentInfo["sensors"] != nil {
				sensorInfos = contentInfo["sensors"].([]interface{})
			}
			pdModel := models.SysDevProduct{}
			pdModel.Id = utils.GetStringValueFiled(pdInfo, "id")
			pdModel.Name = utils.GetStringValueFiled(pdInfo, "name")
			pdModel.DeviceType = utils.GetStringValueFiled(pdInfo, "deviceType")
			pdModel.Status = utils.GetBoolValueField(pdInfo, "status")
			pdModel.ProductModel = utils.GetStringValueFiled(pdInfo, "model")
			pdModel.DeptId = utils.GetStringValueFiled(pdInfo, "deptId")
			pdModel.Photo = utils.GetStringValueFiled(pdInfo, "photo")
			pdModel.Description = utils.GetStringValueFiled(pdInfo, "description")
			if pdInfo["view"] != nil {
				pdModel.View = utils.MapToBytes(pdInfo["view"].(map[string]interface{}))
			}
			now := cDto.LocalTime(time.Now())
			if pdInfo["createTime"] != nil {
				strTime, _ := utils.FormatStrTime(pdInfo["createTime"].(string))
				t := cDto.LocalTime(strTime)
				pdModel.CreateTime = &t
			} else {
				pdModel.CreateTime = &now
			}
			if pdInfo["updateTime"] != nil {
				strTime, _ := utils.FormatStrTime(pdInfo["updateTime"].(string))
				t := cDto.LocalTime(strTime)
				pdModel.UpdateTime = &t
			} else {
				pdModel.UpdateTime = &now
			}
			pdModel.Orm = tx
			// config
			configModel := models.SysDevConfig{}
			configModel.Id = utils.GetStringValueFiled(configInfo, "id")
			configModel.DevSn = utils.GetStringValueFiled(configInfo, "devSn")
			configModel.ProductModel = utils.GetStringValueFiled(configInfo, "productModel")
			configModel.DevProductId = utils.GetStringValueFiled(configInfo, "devProductId")
			if configInfo["data"] != nil {
				configModel.Data = utils.MapToBytes(configInfo["data"].(map[string]interface{}))
			}
			if configInfo["schema"] != nil {
				configModel.Schema = utils.MapToBytes(configInfo["schema"].(map[string]interface{}))
			}
			if configInfo["createTime"] != nil {
				strTime, _ := utils.FormatStrTime(configInfo["createTime"].(string))
				t := cDto.LocalTime(strTime)
				configModel.CreateTime = &t
			} else {
				configModel.CreateTime = &now
			}
			if configInfo["updateTime"] != nil {
				strTime, _ := utils.FormatStrTime(configInfo["updateTime"].(string))
				t := cDto.LocalTime(strTime)
				configModel.UpdateTime = &t
			} else {
				configModel.UpdateTime = &now
			}
			configModel.Orm = tx
			// func
			funcModel := models.SysDevFunc{}
			funcModel.Id = utils.GetStringValueFiled(funcInfo, "id")
			funcModel.DevSn = utils.GetStringValueFiled(funcInfo, "devSn")
			funcModel.ProductModel = utils.GetStringValueFiled(funcInfo, "productModel")
			funcModel.DevProductId = utils.GetStringValueFiled(funcInfo, "devProductId")
			if funcInfo["data"] != nil {
				funcModel.Data = utils.MapToBytes(funcInfo["data"].(map[string]interface{}))
			}
			if funcInfo["schema"] != nil {
				funcModel.Schema = utils.MapToBytes(funcInfo["schema"].(map[string]interface{}))
			}
			if funcInfo["createTime"] != nil {
				strTime, _ := utils.FormatStrTime(funcInfo["createTime"].(string))
				t := cDto.LocalTime(strTime)
				funcModel.CreateTime = &t
			} else {
				funcModel.CreateTime = &now
			}
			if funcInfo["updateTime"] != nil {
				strTime, _ := utils.FormatStrTime(funcInfo["updateTime"].(string))
				t := cDto.LocalTime(strTime)
				funcModel.UpdateTime = &t
			} else {
				funcModel.UpdateTime = &now
			}
			funcModel.Orm = tx
			// sensor
			sensorModel := models.SysDevSensor{}
			sensorModel.Orm = tx
			sensorDatas := make([]models.SysDevSensor, 0)
			for _, v := range sensorInfos {
				sensorInfo := v.(map[string]interface{})
				tmp := models.SysDevSensor{
					Id:           utils.GetStringValueFiled(sensorInfo, "id"),
					Sn:           utils.GetStringValueFiled(sensorInfo, "sn"),
					ProductModel: utils.GetStringValueFiled(sensorInfo, "productModel"),
					Key:          utils.GetStringValueFiled(sensorInfo, "key"),
					Label:        utils.GetStringValueFiled(sensorInfo, "label"),
					Type:         utils.GetStringValueFiled(sensorInfo, "type"),
				}
				if sensorInfo["data"] != nil {
					tmp.Data = utils.MapToBytes(sensorInfo["data"].(map[string]interface{}))
				}
				if sensorInfo["index"] != nil {
					index := utils.GetIntValueFiled(sensorInfo, "index")
					tmp.Index = &index
				}
				if sensorInfo["schema"] != nil {
					tmp.Schema = utils.MapToBytes(sensorInfo["schema"].(map[string]interface{}))
				}
				if sensorInfo["config"] != nil {
					tmp.Config = utils.MapToBytes(sensorInfo["config"].(map[string]interface{}))
				}
				if sensorInfo["param"] != nil {
					tmp.Param = utils.MapToBytes(sensorInfo["param"].(map[string]interface{}))
				}
				if sensorInfo["createTime"] != nil {
					strTime, _ := utils.FormatStrTime(sensorInfo["createTime"].(string))
					t := cDto.LocalTime(strTime)
					tmp.CreateTime = &t
				} else {
					tmp.CreateTime = &now
				}
				if sensorInfo["updateTime"] != nil {
					strTime, _ := utils.FormatStrTime(sensorInfo["updateTime"].(string))
					t := cDto.LocalTime(strTime)
					tmp.UpdateTime = &t
				} else {
					tmp.UpdateTime = &now
				}
				sensorDatas = append(sensorDatas, tmp)
			}

			// 更新or新增
			// 按产品编码更新 认为相同产品编码 是同一产品
			txx := tx.Model(&models.SysDevProduct{}).Scopes(cDto.MakeCondition(query.SysProductQuery{ProductModel: pdModel.ProductModel})).Updates(&pdModel)
			if txx.Error != nil {
				return txx.Error
			}
			if txx.RowsAffected == 0 {
				if err = pdModel.Add(&pdModel); err != nil {
					return err
				}
				if err = configModel.Add(&configModel); err != nil {
					return err
				}
				if err = funcModel.Add(&funcModel); err != nil {
					return err
				}
				if len(sensorDatas) > 0 {
					if err = sensorModel.Add(&sensorDatas); err != nil {
						return err
					}
				}
			} else {
				// 按产品编码更新 — 因为产品是按编码覆盖的
				tx.Model(&models.SysDevConfig{}).Scopes(cDto.MakeCondition(query.SysDevConfigQuery{ProductModel: configModel.ProductModel})).Updates(&configModel)
				tx.Model(&models.SysDevFunc{}).Scopes(cDto.MakeCondition(query.SysDevFuncQuery{ProductModel: funcModel.ProductModel})).Updates(&funcModel)
				if err = sensorModel.Delete(&models.SysDevSensor{}, cDto.MakeCondition(query.SysDevSensorQuery{ProductModel: pdModel.ProductModel})); err != nil {
					return err
				}
				if len(sensorDatas) > 0 {
					if err = sensorModel.Add(&sensorDatas); err != nil {
						return err
					}
				}
			}
		}
		return nil
	})
}

// 查询历史数据(单/多设备 躲变量)
func processHistoryDataReport(db *gorm.DB, req *query.DevHistStatQuery) (err error, result interface{}) {
	sql := "SELECT \n\t update_time, "
	columnSqlList := make([]string, 0)
	unionSqlList := make([]string, 0)
	timeOrder := utils.If(strings.ToUpper(req.TimeOrder) == "DESC", "DESC", "ASC").(string)
	for _, v := range req.SnKeys {
		key := v.Key
		sn := v.Sn
		keyList := strings.Split(key, ".")
		rootKey := keyList[0]
		lastKey := keyList[len(keyList)-1]
		colSql := "MAX ( CASE WHEN KEY = '" + strings.Join(keyList, ",") + "' THEN VALUE END ) AS " + utils.CamelCaseToUdnderscore(lastKey)
		columnSqlList = append(columnSqlList, colSql)
		unionSql := " (select dev_sn,'" + strings.Join(keyList, ",") + "' as key,\n\t"
		unionSql += " value :: jsonb #>> '{" + strings.Join(keyList, ",") + "}' as value,\n\t"
		unionSql += " update_time\n\t"
		unionSql += " from sys_history_sensor\n\t"
		unionSql += " where dev_sn = '" + sn + "'\n\t"
		unionSql += " and value :: jsonb #>> '{" + rootKey + "}' is not null \n\t"
		unionSql += " and date_time >= '" + req.StartTime + "' \n\t"
		unionSql += " and date_time <= '" + req.EndTime + "' \n\t"
		unionSql += " and value :: jsonb #>> '{" + rootKey + "}' is not null )\n\t"
		unionSqlList = append(unionSqlList, unionSql)
	}
	sql += strings.Join(columnSqlList, ",")
	sql += " from\n\t( "
	sql += strings.Join(unionSqlList, " union ")
	sql += ") compose\ngroup by\n\tupdate_time \norder by\n\tupdate_time " + timeOrder + " "

	// 分页or列表
	var resultList = make([]map[string]interface{}, 0)
	var count int64
	if req.PageIndex > 0 && req.PageSize > 0 {
		offset := (req.PageIndex - 1) * req.PageSize
		if offset < 0 {
			offset = 0
		}
		// 查询总数 用于分页
		countSql := "select count(1) from( " + sql + " )alldata"
		if err = db.Raw(countSql).Scan(&count).Error; err != nil {
			return
		}
		sql += "limit ? offset ?"
		if err = db.Raw(sql, req.PageSize, offset).Scan(&resultList).Error; err != nil {
			return
		}

	} else {
		if err = db.Raw(sql).Scan(&resultList).Error; err != nil {
			return
		}
	}

	// Limit(-1).Offset(-1).Count(&count).
	// 设备名称
	//snNameMapper := devname()
	// 处理
	//for i, v := range resultList {
	//	resultList[i].DevName = snNameMapper[v.DevSn]
	//}
	// 矫正顺序
	camelResultList := make([]map[string]interface{}, len(resultList))
	for i, v := range resultList {
		item := make(map[string]interface{})
		for a, b := range v {
			item[utils.UnderscoreToCamel(a)] = b
			camelResultList[i] = item
		}
	}
	// 返回结果
	if req.PageIndex > 0 && req.PageSize > 0 {
		result = &cDto.Page{
			Records:   camelResultList,
			Total:     count,
			PageSize:  req.GetPageSize(),
			PageIndex: req.GetPageIndex(),
			Pages:     (count + int64(req.GetPageSize()) - 1) / int64(req.GetPageSize()),
			Other:     nil,
		}
	} else {
		result = camelResultList
	}
	return
}
